package com.dragon.repository.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dragon.repository.config.annotation.DataScope;
import com.dragon.repository.entity.CustomerPayment;
import com.dragon.repository.entity.PaymentContract;
import com.dragon.repository.entity.PlanCollect;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 回款表 Mapper 接口
 * </p>
 *
 * @author sy
 * @since 2023-07-03
 */
@Mapper
public interface CustomerPaymentMapper extends BaseMapper<CustomerPayment> {

    @Select("<script>" +
            "SELECT M.* FROM (SELECT dcp.*, da.username AS adminName FROM " +
            " dragon_customer_payment dcp " +
            "LEFT JOIN dragon_admin da ON dcp.admin_id = da.id " +
            "ORDER BY dcp.create_time DESC ) M " +
            "${ew.customSqlSegment} " +
            "</script>")
    @DataScope(adminAlias = "M", adminField = "admin_id" , deptAlias = "M", deptField = "department_id",authCodeString = "collectManagement")
    Page<CustomerPayment> paymentList(Page<CustomerPayment> paymentPage, @Param(Constants.WRAPPER) QueryWrapper<CustomerPayment> wrapper);

    @Select("SELECT M.* FROM " +
            "   (SELECT a.*, c.name AS contractName, d.username AS adminName FROM dragon_customer_payment a " +
            "   LEFT JOIN dragon_contract c ON a.contract_id = c.id " +
            "   LEFT JOIN dragon_admin d ON a.admin_id = d.id " +
            "   ) M  WHERE 1=1 AND M.payment_type = 1 " +
            "AND M.contract_id = #{contractId} ")
    List<CustomerPayment> selectCpByContractId(Integer contractId);

    @Select("SELECT M.* FROM (SELECT a.*, d.username AS adminName FROM dragon_customer_payment a " +
            "LEFT JOIN dragon_admin d ON a.admin_id = d.id " +
            ") M  WHERE 1=1 AND M.id = #{id} ")
    CustomerPayment selectByPaymentId(Integer id);

    /**
     * 获取应收金额
     *
     * @param adminId
     * @param year
     * @param yearMonth
     * @return
     */
    @Select("<script>" +
            "SELECT SUM(a.money) FROM dragon_contract a " +
            "WHERE a.payee = #{adminId} " +
            " <if test='year!=null and year != \"\"'> " +
            " AND DATE_FORMAT(a.create_time,'%Y') = #{year}" +
            " </if> " +
            " <if test='yearMonth != null and yearMonth != \"\"'> " +
            " AND DATE_FORMAT(a.create_time,'%Y-%m') = #{yearMonth}" +
            " </if> " +
            "</script>")
    Double selectReceivables(Integer adminId, String year, String yearMonth);

    @Select("<script>" +
            "SELECT sum(a.money) FROM dragon_customer_payment a " +
            "WHERE a.admin_id = #{adminId} " +
            "<if test='year!=null and year != \"\"'> " +
            "AND DATE_FORMAT(a.create_time,'%Y') = #{year}" +
            "</if> " +
            "<if test='yearMonth != null and yearMonth != \"\"'> " +
            "AND DATE_FORMAT(a.create_time,'%Y-%m') = #{yearMonth}" +
            "</if> " +
            "</script> ")
    Double selectPaid(Integer adminId, String year, String yearMonth);

    @Select("<script> " +
            "SELECT " +
            "    sum(case month(a.create_time) when '1'  then (a.money) else 0 end) as '1月', " +
            "    sum(case month(a.create_time) when '2'  then (a.money) else 0 end) as '2月', " +
            "    sum(case month(a.create_time) when '3'  then (a.money) else 0 end) as '3月', " +
            "    sum(case month(a.create_time) when '4'  then (a.money) else 0 end) as '4月', " +
            "    sum(case month(a.create_time) when '5'  then (a.money) else 0 end) as '5月', " +
            "    sum(case month(a.create_time) when '6'  then (a.money) else 0 end) as '6月', " +
            "    sum(case month(a.create_time) when '7'  then (a.money) else 0 end) as '7月', " +
            "    sum(case month(a.create_time) when '8'  then (a.money) else 0 end) as '8月', " +
            "    sum(case month(a.create_time) when '9'  then (a.money) else 0 end) as '9月', " +
            "    sum(case month(a.create_time) when '10' then (a.money)  else 0 end) as '10月', " +
            "    sum(case month(a.create_time) when '11' then (a.money) else 0 end) as '11月', " +
            "    sum(case month(a.create_time) when '12' then (a.money)  else 0 end) as '12月' " +
            "FROM dragon_contract a " +
            "WHERE year(a.create_time)= #{year}  " +
            "<if test=' adminId != null and adminId != \"\"'> " +
            "and a.payee = #{adminId} " +
            "</if> " +
            "</script>")
    Map<String, Object> selectGetReceivablesMoney(Integer adminId, String year);

    @Select("<script> " +
            "select " +
            "    sum(case month(a.create_time) when '1'  then (a.money) else 0 end) as '1月', " +
            "    sum(case month(a.create_time) when '2'  then (a.money) else 0 end) as '2月', " +
            "    sum(case month(a.create_time) when '3'  then (a.money) else 0 end) as '3月', " +
            "    sum(case month(a.create_time) when '4'  then (a.money) else 0 end) as '4月', " +
            "    sum(case month(a.create_time) when '5'  then (a.money) else 0 end) as '5月', " +
            "    sum(case month(a.create_time) when '6'  then (a.money) else 0 end) as '6月', " +
            "    sum(case month(a.create_time) when '7'  then (a.money) else 0 end) as '7月', " +
            "    sum(case month(a.create_time) when '8'  then (a.money) else 0 end) as '8月', " +
            "    sum(case month(a.create_time) when '9'  then (a.money) else 0 end) as '9月', " +
            "    sum(case month(a.create_time) when '10' then (a.money)  else 0 end) as '10月', " +
            "    sum(case month(a.create_time) when '11' then (a.money) else 0 end) as '11月', " +
            "    sum(case month(a.create_time) when '12' then (a.money)  else 0 end) as '12月' " +
            "from dragon_customer_payment a " +
            "where year(a.create_time) = #{year} " +
            "<if test='adminId != null and adminId != \"\"'> " +
            "and a.admin_id = #{adminId} " +
            "</if> " +
            "<if test='type != null and type != \"\"'> " +
            "and a.payment_type = #{type} " +
            "</if> " +
            "</script> ")
    Map<String, Object> selectGetPaidMoney(Integer adminId, String year, Integer type);

    @Select("<script> " +
            "SELECT a.* FROM dragon_customer_payment a WHERE 1=1 " +
            "<if test='time != null and time != \"\"'>" +
            " and  DATE_FORMAT(a.create_time,'%Y-%m') = #{time} " +
            "</if>" +
            "<if test='departmentSet != null and departmentSet.size!=0'>" +
            "AND a.department_id in" +
            "<foreach collection = 'departmentSet' item = 'item' open = '(' close = ')' separator = ','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='adminId != null and adminId != \"\"'>" +
            "AND a.admin_Id = #{adminId} " +
            "</if>" +
            "</script>")
    List<CustomerPayment> selectListByPermissions(String time, Integer adminId, List<String> departmentSet);

    @Select("SELECT MAX(repayment_num) FROM dragon_customer_payment WHERE repayment_num LIKE CONCAT(#{code},'%')")
    String maxRoundInoutNumber(String code);

    @Select("<script>" +
            "SELECT M.* FROM (SELECT dcp.*, da.username AS adminName FROM " +
            " dragon_customer_payment dcp " +
            "LEFT JOIN dragon_admin da ON dcp.admin_id = da.id " +
            " ) M " +
            "${ew.customSqlSegment} " +
            "</script>")
    Page<CustomerPayment> selectPageByWrapper(Page<CustomerPayment> paymentPage, @Param(Constants.WRAPPER) QueryWrapper<CustomerPayment> wrapper);

    /**
     * 实收月
     * @return
     */
    @Select("SELECT SUM(money)  FROM dragon_customer_payment  where payment_status = 1 and LEFT(create_time,7) = LEFT(NOW(),7)")
    @DataScope( adminField = "admin_id",  deptField = "department_id")
    Double selectMonthActualMoney();

    /**
     * 实收总
     * @return
     */
    @Select("SELECT SUM(money)  FROM dragon_customer_payment where status=1 and payment_status = 1  ")
    @DataScope( adminField = "admin_id", deptField = "department_id")
    Double selectYearActualMoney();

    /**
     * 实收 年金额
     * @param year
     * @return
     */
    @Select("SELECT SUM(money)  FROM dragon_customer_payment  WHERE status = 1 and payment_status = 1 AND LEFT(create_time,4) = #{year}")
    @DataScope( adminField = "admin_id", deptField = "department_id")
    Double selectMoneyForYear(String year);

    @Select("with base as(" +
            "    select money,create_time from dragon_customer_payment where status = 1 and payment_status = 1" +
            ")," +
            "totalMoney as (" +
            "    select sum(money) totalPaymentMoney from base" +
            ")," +
            "yearMoney as (" +
            "    select sum(money) yearPaymentMoney from base where left(create_time,4) = left(now(),4)" +
            ")," +
            "monthMoney as (" +
            "    select sum(money) monthPaymentMoney from base where left(create_time,7) = left(now(),7)" +
            ")," +
            "makeData as (" +
            "    select a.totalPaymentMoney,b.yearPaymentMoney,c.monthPaymentMoney from totalMoney a,yearMoney b,monthMoney c" +
            ")" +
            "select * from makeData")
    Map<String,Object> selectListStatistics();

    /**
     * 应收每个月的统计
     * @param time
     * @return
     */
    @Select("select " +
            "IFNULL(SUM(case month(create_time) when '1'  then money else 0 end),0) as '1月', " +
            "IFNULL(sum(case month(create_time) when '2'  then money else 0 end),0) as '2月', " +
            "IFNULL(sum(case month(create_time) when '3'  then money else 0 end),0) as '3月', " +
            "IFNULL(sum(case month(create_time) when '4'  then money else 0 end),0) as '4月', " +
            "IFNULL(sum(case month(create_time) when '5'  then money else 0 end),0) as '5月', " +
            "IFNULL(sum(case month(create_time) when '6'  then money else 0 end),0) as '6月'," +
            "IFNULL(sum(case month(create_time) when '7'  then money else 0 end),0) as '7月', " +
            "IFNULL(sum(case month(create_time) when '8'  then money else 0 end),0) as '8月', " +
            "IFNULL(sum(case month(create_time) when '9'  then money else 0 end),0) as '9月', " +
            "IFNULL(sum(case month(create_time) when '10' then money  else 0 end),0) as '10月', " +
            "IFNULL(sum(case month(create_time) when '11' then money  else 0 end),0) as '11月',  " +
            "IFNULL(sum(case month(create_time) when '12' then money  else 0 end),0) as '12月'  " +
            "FROM dragon_customer_payment  where status = 1 and payment_status = 1 AND YEAR(create_time) = #{time} ")
    LinkedHashMap<String, Object> customerPaymentMonthlyStatistics(String time);
}
